
import pandas as pd
import numpy as np
from pmdarima import auto_arima
import plotly.graph_objects as go
from sklearn.metrics import mean_absolute_error, mean_squared_error
import os


# !pip install -U kaleido # you need to install for the visualization


# Read the Excel file
# Data_Status: Indicates the status of the data. The value "2020F" suggests that it is a forecast for the year 2020.
# State: Represents the state for which the data is recorded (in this case, "CA" for California).
# MSN: Stands for "Monthly State Names" and refers to the specific energy metric or variable being measured. Examples include ARICD, ARICV, ARTCD, ARTCV, ARTXD, WWTXV, WXICD, WXICV, ZWCDP, ZWHDP.
df = pd.read_csv('Datasets/pr_CA.csv')
df.drop('Data_Status',axis=1,inplace=True)

df


df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 304 entries, 0 to 303
Data columns (total 53 columns):
 #   Column  Non-Null Count  Dtype  
---  ------  --------------  -----  
 0   State   304 non-null    object 
 1   MSN     304 non-null    object 
 2   1970    284 non-null    float64
 3   1971    284 non-null    float64
 4   1972    284 non-null    float64
 5   1973    284 non-null    float64
 6   1974    284 non-null    float64
 7   1975    284 non-null    float64
 8   1976    284 non-null    float64
 9   1977    284 non-null    float64
 10  1978    284 non-null    float64
 11  1979    284 non-null    float64
 12  1980    284 non-null    float64
 13  1981    284 non-null    float64
 14  1982    284 non-null    float64
 15  1983    284 non-null    float64
 16  1984    284 non-null    float64
 17  1985    284 non-null    float64
 18  1986    284 non-null    float64
 19  1987    284 non-null    float64
 20  1988    284 non-null    float64
 21  1989    284 non-null    float64
 22  1990    284 non-null    float64
 23  1991    284 non-null    float64
 24  1992    284 non-null    float64
 25  1993    280 non-null    float64
 26  1994    280 non-null    float64
 27  1995    280 non-null    float64
 28  1996    280 non-null    float64
 29  1997    283 non-null    float64
 30  1998    283 non-null    float64
 31  1999    283 non-null    float64
 32  2000    283 non-null    float64
 33  2001    283 non-null    float64
 34  2002    283 non-null    float64
 35  2003    283 non-null    float64
 36  2004    283 non-null    float64
 37  2005    283 non-null    float64
 38  2006    283 non-null    float64
 39  2007    283 non-null    float64
 40  2008    283 non-null    float64
 41  2009    283 non-null    float64
 42  2010    300 non-null    float64
 43  2011    300 non-null    float64
 44  2012    300 non-null    float64
 45  2013    300 non-null    float64
 46  2014    300 non-null    float64
 47  2015    300 non-null    float64
 48  2016    300 non-null    float64
 49  2017    300 non-null    float64
 50  2018    300 non-null    float64
 51  2019    300 non-null    float64
 52  2020    300 non-null    float64
dtypes: float64(51), object(2)
memory usage: 126.0+ KB


df.describe()


# transposed_df = df.set_index(['Data_Status', 'State', 'MSN']).T.reset_index()
# # Convert the Timestamp column to datetime
# transposed_df['Timestamp'] = pd.to_datetime(transposed_df['Timestamp'])


df# Assuming your DataFrame is called 'df'
df_trans = df.melt(id_vars=['State', 'MSN'], var_name='Year', value_name='Yearly Data')
# df['Year'] = pd.to_datetime(df['Year'], format='%Y')

# Set the 'State', 'MSN', and 'Year' columns as the index
df_trans.set_index(['State', 'MSN', 'Year'], inplace=True)

# Sort the index in ascending order
df_trans.sort_index(inplace=True)

# Print the resulting time series DataFrame
df_trans.reset_index(inplace=True)
df_trans['Year'] = pd.to_datetime(df_trans['Year'], format='%Y')

df_trans.head()


os.makedirs('Plots/Arima_results_plots',exist_ok=True)

for State in df_trans['State'].unique():
    for msn in df_trans['MSN'].unique():
        try:
            
            fig = go.Figure()

            print('State : {} and MSN : {}'.format(State,msn))
            # Get the energy consumption data for the current country and sector
            df_filter = df_trans[(df_trans['State'] == State) & (
                df_trans['MSN'] == msn)][['Year', 'Yearly Data']]
            df_filter_index = df_filter.set_index('Year')

            train_data = df_filter[:-5]
            test_data = df_filter[-5:]
            
            # Prepare the data for modeling
            years = df_filter_index.index
            energy_consumption = df_filter_index.values.flatten()

                    # Split the data into training and testing
            # Use all data except the last 5 years for training
            Horizan = -5
            train_data = energy_consumption[:Horizan]
            test_data = energy_consumption[Horizan:]  # Use the last 5 years for testing

            # Fit the auto ARIMA model
            model = auto_arima(train_data, seasonal=False)
            model.fit(train_data)

            # Generate predictions
            predictions = model.predict(n_periods=len(test_data))
            predictions_ahead_in_future = model.predict(n_periods=len(test_data)+15)

            # Calculate evaluation metrics
            mae = mean_absolute_error(test_data, predictions)
            mse = mean_squared_error(test_data, predictions)
            mape = np.mean(np.abs((test_data - predictions) / test_data)) * 100

            print('Mean Absolute Error (MAE):', np.round(mae,2))
            print('Mean Squared Error (MSE):', np.round(mse,2))
            print('Mean Absolute Percentage Error (MAPE):', np.round(mape,2))
            
            # Plot the training data
            fig.add_trace(go.Scatter(
                x=years[:Horizan], y=train_data, mode='lines+markers', name='Training Data'))

            # Plot the predictions
            fig.add_trace(go.Scatter(
                x=years[Horizan:], y=test_data, mode='lines+markers', name='Actual'))
            fig.add_trace(go.Scatter(
                x=years[Horizan:], y=predictions, mode='lines+markers', name='Predicted'))

            fig.add_trace(go.Scatter(
                x=pd.date_range(start = years[Horizan],periods=15,freq='Y'), y=predictions_ahead_in_future, mode='lines+markers', name='Prediction till 2030'))

            # Update the layout
            fig.update_layout(title=f'Energy Consumption Forecast State using ARIMA : {State} : MSN : {msn} ',
                            xaxis_title='Year', yaxis_title='Energy Consumption')

            # Show the plot
            fig.show()
            print(State,msn)
            fig.write_image(f'Plots/Arima_results_plots/{State}_{msn}.png')
            # break
        except:
            print('Error occoured in Combination State : {} and MSN : {} Due NaN Value'.format(State,mse))

State : CA and MSN : ARICD
Mean Absolute Error (MAE): 1.7
Mean Squared Error (MSE): 3.99
Mean Absolute Percentage Error (MAPE): 15.68

CA ARICD
State : CA and MSN : ARICV
Mean Absolute Error (MAE): 254.19
Mean Squared Error (MSE): 68574.32
Mean Absolute Percentage Error (MAPE): 39.24

CA ARICV
State : CA and MSN : ARTCD
Mean Absolute Error (MAE): 1.7
Mean Squared Error (MSE): 3.99
Mean Absolute Percentage Error (MAPE): 15.68

CA ARTCD
State : CA and MSN : ARTCV
Mean Absolute Error (MAE): 254.19
Mean Squared Error (MSE): 68574.32
Mean Absolute Percentage Error (MAPE): 39.24

CA ARTCV
State : CA and MSN : ARTXD
Mean Absolute Error (MAE): 1.7
Mean Squared Error (MSE): 3.99
Mean Absolute Percentage Error (MAPE): 15.68

CA ARTXD
State : CA and MSN : ARTXV
Mean Absolute Error (MAE): 254.19
Mean Squared Error (MSE): 68574.32
Mean Absolute Percentage Error (MAPE): 39.24

CA ARTXV
State : CA and MSN : AVACD
Mean Absolute Error (MAE): 1.88
Mean Squared Error (MSE): 4.53
Mean Absolute Percentage Error (MAPE): 8.03

CA AVACD
State : CA and MSN : AVACV
Mean Absolute Error (MAE): 11.86
Mean Squared Error (MSE): 198.7
Mean Absolute Percentage Error (MAPE): 20.67

CA AVACV
State : CA and MSN : AVTCD
Mean Absolute Error (MAE): 1.88
Mean Squared Error (MSE): 4.53
Mean Absolute Percentage Error (MAPE): 8.03

CA AVTCD
State : CA and MSN : AVTCV
Mean Absolute Error (MAE): 11.86
Mean Squared Error (MSE): 198.7
Mean Absolute Percentage Error (MAPE): 20.67


os.makedirs('Plots/Sarima_results_plots',exist_ok=True)

for State in df_trans['State'].unique():
    for msn in df_trans['MSN'].unique():
        try:
            
            fig = go.Figure()

            print('State : {} and MSN : {}'.format(State,msn))
            # Get the energy consumption data for the current country and sector
            df_filter = df_trans[(df_trans['State'] == State) & (
                df_trans['MSN'] == msn)][['Year', 'Yearly Data']]
            df_filter_index = df_filter.set_index('Year')

            train_data = df_filter[:-5]
            test_data = df_filter[-5:]
            
            # Prepare the data for modeling
            years = df_filter_index.index
            energy_consumption = df_filter_index.values.flatten()

                    # Split the data into training and testing
            # Use all data except the last 5 years for training
            Horizan = -5
            train_data = energy_consumption[:Horizan]
            test_data = energy_consumption[Horizan:]  # Use the last 5 years for testing

            # Fit the auto ARIMA model
            model = auto_arima(train_data, seasonal=True)
            model.fit(train_data)

            # Generate predictions
            predictions = model.predict(n_periods=len(test_data))
            predictions_ahead_in_future = model.predict(n_periods=len(test_data)+15)

            # Calculate evaluation metrics
            mae = mean_absolute_error(test_data, predictions)
            mse = mean_squared_error(test_data, predictions)
            mape = np.mean(np.abs((test_data - predictions) / test_data)) * 100

            print('Mean Absolute Error (MAE):', np.round(mae,2))
            print('Mean Squared Error (MSE):', np.round(mse,2))
            print('Mean Absolute Percentage Error (MAPE):', np.round(mape,2))
            
            # Plot the training data
            fig.add_trace(go.Scatter(
                x=years[:Horizan], y=train_data, mode='lines+markers', name='Training Data'))

            # Plot the predictions
            fig.add_trace(go.Scatter(
                x=years[Horizan:], y=test_data, mode='lines+markers', name='Actual'))
            fig.add_trace(go.Scatter(
                x=years[Horizan:], y=predictions, mode='lines+markers', name='Predicted'))

            fig.add_trace(go.Scatter(
                x=pd.date_range(start = years[Horizan],periods=15,freq='Y'), y=predictions_ahead_in_future, mode='lines+markers', name='Prediction till 2030'))

            # Update the layout
            fig.update_layout(title=f'Energy Consumption Forecast State using SARIMA : {State} : MSN : {msn} ',
                            xaxis_title='Year', yaxis_title='Energy Consumption')

            # Show the plot
            fig.show()
            print(State,msn)
            fig.write_image(f'Plots/Sarima_results_plots/{State}_{msn}.png')
            # break
        except:
            print('Error occoured in Combination State : {} and MSN : {} Due NaN Value'.format(State,mse))
        break

State : CA and MSN : ARICD
Mean Absolute Error (MAE): 1.7
Mean Squared Error (MSE): 3.99
Mean Absolute Percentage Error (MAPE): 15.68

CA ARICD

	State	MSN	1970	1971	1972	1973	1974	1975	1976	1977	...	2011	2012	2013	2014	2015	2016	2017	2018	2019	2020
0	CA	ARICD	0.49	0.64	0.64	0.69	1.47	1.62	1.60	1.70	...	14.83	16.79	15.59	15.97	13.57	10.04	9.90	12.56	13.42	11.36
1	CA	ARICV	39.00	51.90	52.30	60.50	115.90	141.00	149.60	169.10	...	891.20	903.80	937.00	916.30	834.50	614.10	592.70	758.60	744.60	636.30
2	CA	ARTCD	0.49	0.64	0.64	0.69	1.47	1.62	1.60	1.70	...	14.83	16.79	15.59	15.97	13.57	10.04	9.90	12.56	13.42	11.36
3	CA	ARTCV	39.00	51.90	52.30	60.50	115.90	141.00	149.60	169.10	...	891.20	903.80	937.00	916.30	834.50	614.10	592.70	758.60	744.60	636.30
4	CA	ARTXD	0.49	0.64	0.64	0.69	1.47	1.62	1.60	1.70	...	14.83	16.79	15.59	15.97	13.57	10.04	9.90	12.56	13.42	11.36
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
299	CA	WWTXV	55.40	57.00	61.70	64.40	70.80	67.40	77.70	83.90	...	366.10	343.30	409.30	410.50	190.40	163.30	171.50	188.80	215.50	198.10
300	CA	WXICD	4.14	3.97	4.23	4.63	4.63	4.95	5.51	6.26	...	35.56	34.62	33.37	33.91	32.88	32.66	32.69	32.94	29.79	26.87
301	CA	WXICV	10.50	10.10	11.00	15.50	15.20	14.50	21.70	20.60	...	70.50	69.60	74.20	67.60	54.80	56.60	44.10	54.20	41.20	32.80
302	CA	ZWCDP	748.00	738.00	748.00	681.00	750.00	596.00	651.00	739.00	...	774.00	1020.00	965.00	1175.00	1158.00	1024.00	1166.00	1102.00	894.00	1206.00
303	CA	ZWHDP	3169.00	3690.00	3278.00	3396.00	3297.00	3609.00	3031.00	3050.00	...	3182.00	2741.00	2707.00	2091.00	2342.00	2438.00	2442.00	2539.00	2946.00	2564.00

	1970	1971	1972	1973	1974	1975	1976	1977	1978	1979	...	2011	2012	2013	2014	2015	2016	2017	2018	2019	2020
count	2.840000e+02	2.840000e+02	2.840000e+02	2.840000e+02	2.840000e+02	2.840000e+02	2.840000e+02	2.840000e+02	2.840000e+02	2.840000e+02	...	3.000000e+02	3.000000e+02	3.000000e+02	3.000000e+02	3.000000e+02	3.000000e+02	3.000000e+02	3.000000e+02	3.000000e+02	3.000000e+02
mean	9.011960e+04	9.429002e+04	9.581337e+04	9.975289e+04	9.443671e+04	9.668779e+04	1.000583e+05	1.058091e+05	1.052697e+05	1.111674e+05	...	1.269225e+05	1.261850e+05	1.279828e+05	1.270545e+05	1.289333e+05	1.301465e+05	1.326062e+05	1.345600e+05	1.352105e+05	1.195288e+05
std	4.455932e+05	4.663730e+05	4.742706e+05	4.898451e+05	4.605783e+05	4.732360e+05	4.900167e+05	5.219472e+05	5.213416e+05	5.446264e+05	...	5.928098e+05	5.891410e+05	5.942789e+05	5.898692e+05	6.039160e+05	6.113764e+05	6.211631e+05	6.268406e+05	6.290843e+05	5.471620e+05
min	0.000000e+00	0.000000e+00	-1.000000e-01	0.000000e+00	0.000000e+00	0.000000e+00	0.000000e+00	0.000000e+00	0.000000e+00	0.000000e+00	...	0.000000e+00	0.000000e+00	0.000000e+00	0.000000e+00	0.000000e+00	0.000000e+00	0.000000e+00	0.000000e+00	0.000000e+00	0.000000e+00
25%	7.300000e-01	7.925000e-01	8.075000e-01	1.000000e+00	1.725000e+00	2.110000e+00	2.320000e+00	2.365000e+00	2.642500e+00	3.550000e+00	...	2.162500e+01	1.679000e+01	1.609250e+01	1.597000e+01	1.163000e+01	1.034000e+01	1.194000e+01	1.481000e+01	1.342000e+01	1.025000e+01
50%	4.890000e+00	5.270000e+00	5.580000e+00	5.330000e+00	7.940000e+00	8.720000e+00	1.064500e+01	1.485000e+01	1.840000e+01	2.970000e+01	...	4.640000e+01	4.377500e+01	5.430000e+01	5.805000e+01	5.195000e+01	5.004000e+01	4.950000e+01	5.775000e+01	6.017000e+01	4.744000e+01
75%	4.302250e+02	4.692750e+02	5.253250e+02	6.664500e+02	8.611000e+02	1.258050e+03	1.463650e+03	1.897400e+03	1.787400e+03	2.088600e+03	...	6.959725e+03	5.292775e+03	8.987525e+03	9.074500e+03	6.001700e+03	5.310900e+03	6.788900e+03	8.436250e+03	8.251150e+03	6.458750e+03
max	4.472838e+06	4.668262e+06	4.765624e+06	4.911626e+06	4.530411e+06	4.658922e+06	4.831220e+06	5.243449e+06	5.083948e+06	5.415031e+06	...	5.685695e+06	5.661024e+06	5.670996e+06	5.582047e+06	5.694129e+06	5.663687e+06	5.664659e+06	5.668087e+06	5.641205e+06	4.869977e+06

using CA Datasets¶

Data Satatics¶

After Transformation of Data¶

Modeling the Data¶

Using ARIMA Model¶

Using Sarima¶

	State	MSN	Year	Yearly Data
0	CA	ARICD	1970-01-01	0.49
1	CA	ARICD	1971-01-01	0.64
2	CA	ARICD	1972-01-01	0.64
3	CA	ARICD	1973-01-01	0.69
4	CA	ARICD	1974-01-01	1.47